from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey,PickleType
# from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.types import JSON 
from sqlalchemy import Table, Column, String
from sqlalchemy import Table, Column, Integer, String, MetaData
from sqlalchemy.orm import declarative_base

# 创建数据库引擎（使用SQLite）
db_url = "sqlite:///DataBase/Main.db"  
engine = create_engine(db_url)
Base = declarative_base()

#第四张表：联查表
class Phrase(Base):
    __tablename__ = 'phrases'

    id = Column(Integer, primary_key=True,autoincrement=True)
    text = Column(String(10))
    tag = Column(Integer, default=0)
    url_list = Column(JSON)

Base.metadata.create_all(engine)

def update_phrase(new_word, video_url):
    # 创建新的会话对象
    Session = sessionmaker(bind=engine)
    session = Session()

    # 尝试从数据库中查找匹配的词
    video_entry = session.query(Phrase).filter_by(text=new_word).first()

    if video_entry:
        print("Record exists:", video_entry.text)
        video_entry.tag = 1
        if video_url not in video_entry.url_list:
            new_list = video_entry.url_list.copy()
            new_list.append(video_url)
            video_entry.url_list = new_list
            print(video_entry.url_list, "test0")
            session.commit()
            print(video_entry.url_list, "test1")
        else:
            print("error")
    else:
        print("Record does not exist. Creating new record.")
        new_entry = Phrase(text=new_word, tag=0, url_list=[video_url])
        print(new_entry.url_list, "test2")
        session.add(new_entry)
        session.commit()

    # 提交更改并关闭会话

    session.close()


# 测试用例
word_input = "example"
url_input = "http://example.com/video1"
url_input2 = "http://example.com/video2"
update_phrase(word_input, url_input)
update_phrase(word_input, url_input2)
Session = sessionmaker(bind=engine)
session = Session()
# 查询并打印Phrase表的所有记录
phrases = session.query(Phrase).all()
session.close()
for phrase in phrases:
    print(f"ID: {phrase.id}, Text: {phrase.text}, Tag: {phrase.tag}, URL List: {phrase.url_list},{len(phrase.url_list)}")
